package net.huashitong.Indicators.dao;

import com.sirdc.modules.core.dao.GenericQuery;
import com.sirdc.modules.core.dao.StringDao;
import com.sirdc.modules.utils.ObjectUtils;
import com.sirdc.modules.utils.StringUtils;
import net.huashitong.Indicators.dataBean.IndexDataDTO;
import net.huashitong.Indicators.entity.Industry;
import net.huashitong.Indicators.entity.OneIndex;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class OneIndexDao extends StringDao<OneIndex> {


    /**
     * 拿到type下所有指标值
     *
     * @param codeType
     * @return
     */
    public List<OneIndex> getAllIndex(String codeType, String areaType) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.codeType=:codeType").setParam("codeType", codeType);
        query.append(" and obj.areaType=:areaType").setParam("areaType", areaType);
        return query.listResult();
    }


    /**
     * 通过codeId查询
     *
     * @param codeId
     * @return
     */
    public OneIndex queryByCodeId(String codeId) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.codeId=:codeId").setParam("codeId", codeId);
        return query.firstResult();
    }


    /**
     * 月度数据
     * 首页主目录子节点
     */
    public List<OneIndex> getChileCatalogue(String parentCode, String codeNode) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.parentCode like:parentCode").setParam("parentCode", parentCode);
        query.append(" and obj.codeType='Y' ");
        query.append(" and obj.codeNode=:codeNode ").setParam("codeNode", codeNode);

        return query.listResult();
    }


    /**
     * 所有二级指标
     *
     * @return
     */
    public List<OneIndex> getTwoNode(String codetype, String codeNode, String areaType) {
        GenericQuery query = createQuery("obj");
        if (ObjectUtils.isNotBlank(codeNode)) {
            query.append(" and obj.codeNode=:codeNode").setParam("codeNode", codeNode);
        }
        query.append(" and obj.codeType=:codeType").setParam("codeType", codetype);
        query.append(" and obj.areaType=:areaType").setParam("areaType", areaType);
        query.append(" order by obj.codeId asc");
        return query.listResult();
    }


    public List<OneIndex> getIndexFromTemp() {
        StringBuffer sql = new StringBuffer("select " +
                "area_code \"areaCode\",area_name \"areaName\",area_type \"areaType\"," +
                "code_id \"codeId\",code_name \"codeName\",code_node \"codeNode\"," +
                "code_type \"codeType\",cumulative_ratio \"cumulativeRatio\"," +
                "cumulative_value \"cumulativeValue\",current_value \"currentValue\"," +
                "datetime \"datetime\",explanation \"explanation\",isdata \"isdata\",parent_code \"parentCode\"," +
                "ranks \"ranks\",unit \"unit\" " +
                "from AB01_TEMP");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(OneIndex.class));
        List<OneIndex> list = (List<OneIndex>) query.list();
        return list;
    }


    /**
     * 根据父节点ID拿到子节点
     */
    public List<OneIndex> getChileNode(String pareentCode, String codeType, String areaType) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.parentCode=:parentCode").setParam("parentCode", pareentCode);
        query.append(" and obj.codeType=:codeType ").setParam("codeType", codeType);
        query.append(" and obj.areaType=:areaType").setParam("areaType", areaType);
        return query.listResult();
    }

  /*  public List<OneIndex> getIndextest (){
        StringBuffer sql  = new StringBuffer();
        sql.append("select  a.code_id \"codeId\",a.code_name \"codeName\" ,a.parent_code \"parentCode\" ");
        sql.append("from ab01 a  ,ab01 b ");
        sql.append("where a.parent_code =b.code_id ");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(OneIndex.class));
        List<OneIndex> list  = (List<OneIndex>)query.list();
        return list;
    }
*/

    public void deleteOneIndex(String codeType, String areaType) {
        StringBuffer sql = new StringBuffer();
        sql.append(" delete from OneIndex obj ");
        sql.append(" where 1=1 ");
        sql.append(" and obj.codeType=:codeType ");
        sql.append(" and obj.areaType=:areaType ");
        GenericQuery query = create(sql.toString());
        query.setParam("codeType", codeType);
        query.setParam("areaType", areaType);
        query.executeUpdate();
    }

    public void deleteIndexByCodeNode(String codeType, String areaType, String codeNode) {
        StringBuffer sql = new StringBuffer();
        sql.append(" delete from OneIndex obj ");
        sql.append(" where 1=1 ");
        sql.append(" and obj.codeType=:codeType ");
        sql.append(" and obj.areaType=:areaType ");
        sql.append(" and obj.codeNode=:codeNode ");
        GenericQuery query = create(sql.toString());
        query.setParam("codeType", codeType);
        query.setParam("areaType", areaType);
        query.setParam("codeNode", codeNode);
        query.executeUpdate();
    }

    public void updateOneIndexInfo(String codeType, OneIndex index) {
        StringBuffer sql = new StringBuffer();
        sql.append(" update OneIndex obj ");
        sql.append(" set obj.explanation =:explanation ");
        sql.append(" ,obj.unit =:unit ");
        sql.append(" where obj.codeId=:codeId ");
        sql.append(" and obj.codeType=:codeType ");
        GenericQuery query = create(sql.toString());
        query.setParam("explanation", index.getExplanation());
        query.setParam("unit", index.getUnit());
        query.setParam("codeId", index.getCodeId());
        query.setParam("codeType", index.getCodeType());
        query.executeUpdate();
    }


    /**
     * 根据areaType 获取 目录
     */
    public List<OneIndex> getViewBy(String areType, String codeNode, String parentCode) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.areaType=:areaType ").setParam("areaType", areType);
        if (StringUtils.isNotBlank(codeNode)) {
            query.append(" and obj.codeNode=:codeNode ").setParam("codeNode", codeNode);
        }
        if (StringUtils.isNotBlank(parentCode))
            query.append(" and obj.parentCode=:parentCode ").setParam("parentCode", parentCode);

        return query.listResult();
    }


    /**
     * 根据时间类型和地区类型获取指标
     *
     * @param areaType
     * @param codeType
     * @return
     */
    public List<OneIndex> queryOneIndexByAreaTypeAndCodeType(String areaType, String codeType) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.areaType=:areaType ").setParam("areaType", areaType);
        query.append(" and obj.codeType=:codeType ").setParam("codeType", codeType);
        return query.listResult();
    }

    /**
     * 根据指标Id 获取中文名
     *
     * @param code_id
     * @param codeType
     * @param areaType
     * @return
     */
    public List<OneIndex> getIndexNameById(String code_id, String codeType, String areaType, String datetime) {
        GenericQuery query = createQuery(" t");
        query.append(" and t.codeId=:codeId ").setParam("codeId", code_id);
        query.append(" and t.areaType=:areaType ").setParam("areaType", areaType);
        query.append(" and t.codeType=:codeType ").setParam("codeType", codeType);
        if (StringUtils.isNotBlank(datetime)) {
            query.append(" and t.datetime=:datetime ").setParam("datetime", datetime);
        }
        return query.listResult();
    }

    /**
     * 获取节点指标
     */
    public List<OneIndex> getNodeCodeId(String lastNode, String currentNode, String codeType, String area_type) {
        StringBuffer sql = new StringBuffer();
        sql.append("select  b.code_id \"codeId\" ,b.parent_code \"parentCode\" ,b.code_type \"codeType\" ,b.area_type \"areaType\" ");
        sql.append("from AB01 A, AB01 B ");
        sql.append("where A.area_type = '" + area_type + "'");
        sql.append("and A.code_type = '" + codeType + "'");
        sql.append("AND A.CODE_NODE = '" + lastNode + "' ");
        sql.append("AND A.PARENT_CODE = B.CODE_ID ");
        sql.append("AND A.AREA_TYPE =B.AREA_TYPE ");
        sql.append("AND A.CODE_TYPE=B.CODE_TYPE ");
        sql.append("AND B.CODE_NODE = '" + currentNode + "'");
        sql.append("AND B.area_type = '" + area_type + "'");
        sql.append("and B.code_type = '" + codeType + "'");
        sql.append("group by  b.code_id ,b.parent_code ,b.code_type ,b.area_type ");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(OneIndex.class));
        List<OneIndex> dtos = (List<OneIndex>) query.list();

        return dtos;
    }


}
